package hu.myai.util;

import hu.myai.model.db.AxEstimation;
import hu.myai.model.db.AxHistory;

import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.NoResultException;

import org.joone.log.ILogger;
import org.joone.log.LoggerFactory;

public class DatabaseUtil {

	private static final ILogger log = LoggerFactory.getLogger(DatabaseUtil.class);

	public EntityManager entityManager = Constans.emf.createEntityManager();

	public DatabaseUtil() {
	}

	public AxHistory getSavedHistory(Date date, String symbol) {

		try {
			return (AxHistory) entityManager.createQuery(
					"select h from hu.myai.model.db.AxHistory h where h.date = :date and h.symbol = :symbol")
					.setParameter("date", date).setParameter("symbol", symbol).getSingleResult();

		} catch (RuntimeException ex) {
			log.debug("Error - getSavedHistory - not a single history records: " + ex.getMessage());
			return null;
		}
	}

	public Integer getSavedHistoryID(Date date, String symbol) {
		Integer retValue = null;
		try {
			retValue = (Integer) entityManager.createQuery(
					"select h.id from hu.myai.model.db.AxHistory h where h.date = :date and h.symbol = :symbol")
					.setParameter("date", date).setParameter("symbol", symbol).getSingleResult();

		} catch (RuntimeException ex) {
			log.debug("Error - getSavedHistoryID - not a single history records: " + ex.getMessage());
		}
		return retValue;
	}

	@SuppressWarnings("unchecked")
	public List<AxHistory> getSavedHistoryList(Date date, String symbol, int maxSize) {

		// set the date as early as possible
		if (date == null) {
			date = new Date(0);
		}

		try {
			// get the history from the database
			return entityManager
					.createQuery(
							"select h from hu.myai.model.db.AxHistory h where h.date > :date and h.symbol = :symbol order by date")
					.setParameter("date", date).setParameter("symbol", symbol).setMaxResults(maxSize).getResultList();

		} catch (RuntimeException ex) {
			log.debug("Error - getSavedHistory - not a single history records: " + ex.getMessage());
			return null;
		}
	}

	@SuppressWarnings("unchecked")
	public double[][] getHistoryForExecute(Date date, String symbol) {

		double[][] resArray = null;

		if (date == null) {
			date = new Date();
			date.setTime(0);
		}

		try {
			List<AxHistory> historyArray = entityManager
					.createQuery(
							"select h from hu.myai.model.db.AxHistory h where h.date > :date and h.symbol = :symbol order by date")
					.setParameter("date", date).setParameter("symbol", symbol).getResultList();

			if (historyArray.size() > 0)
				resArray = new double[historyArray.size()][11];

			for (int i = 0; i < historyArray.size(); i++) {
				resArray[i][0] = new Double(historyArray.get(i).getDate().getTime());
				resArray[i][1] = historyArray.get(i).getOpen();
				resArray[i][2] = historyArray.get(i).getHigh();
				resArray[i][3] = historyArray.get(i).getLow();
				resArray[i][4] = historyArray.get(i).getClose();
				resArray[i][5] = new Double(historyArray.get(i).getVolume());

				Integer bookBuy = historyArray.get(i).getBookBuy();
				// get the closet value
				if (bookBuy == null) {
					Integer upBookBuy = null;
					int j = i;
					while (++j < historyArray.size() && (upBookBuy = historyArray.get(j).getBookBuy()) == null)
						;
					Integer downBookBuy = null;
					int k = i;
					while (--k > 0 && (downBookBuy = historyArray.get(k).getBookBuy()) == null)
						;

					bookBuy = ((upBookBuy != null ? upBookBuy : 0) + (downBookBuy != null ? downBookBuy : 0)) / 2;
				}
				resArray[i][6] = new Double(bookBuy);

				Integer bookSell = historyArray.get(i).getBookSell();
				// get the closet value
				if (bookSell == null) {
					Integer upBookSell = null;
					int j = i;
					while (++j < historyArray.size() && (upBookSell = historyArray.get(j).getBookSell()) == null)
						;
					Integer downBookSell = null;
					int k = i;
					while (--k > 0 && (downBookSell = historyArray.get(k).getBookSell()) == null)
						;

					bookSell = ((upBookSell != null ? upBookSell : 0) + (downBookSell != null ? downBookSell : 0)) / 2;
				}
				resArray[i][7] = new Double(bookSell);

				// for validation, close
				resArray[i][8] = resArray[i][4];
				// for validation, close
				resArray[i][9] = resArray[i][4];
				// for validation, low
				resArray[i][10] = resArray[i][3];

			}

		} catch (RuntimeException ex) {
			log.debug("Error - getHistoryForExecute - " + ex.getMessage());
			ex.printStackTrace();
		}
		return resArray;
	}

	public AxEstimation getSavedEstimation(AxHistory history, String nnet) {

		try {
			return (AxEstimation) entityManager
					.createQuery(
							"select e from hu.myai.model.db.AxEstimation e left join e.axHistory h where e.nnet = :nnet and h = :history ")
					.setParameter("nnet", nnet).setParameter("history", history).getSingleResult();

		} catch (RuntimeException ex) {
			log.debug("Error - getSavedEstimation - not a single estimation records: " + ex.getMessage());
			return null;
		}
	}

	public AxEstimation getSavedEstimation(Date date, String symbol, String nnet) {

		try {
			return (AxEstimation) entityManager
					.createQuery(
							"select e from hu.myai.model.db.AxEstimation e left join e.axHistory h where e.nnet = :nnet and h.date = :date and h.symbol = :symbol ")
					.setParameter("nnet", nnet).setParameter("date", date).setParameter("symbol", symbol)
					.getSingleResult();

		} catch (RuntimeException ex) {
			log.debug("Error - getSavedEstimation - not a single estimation records: " + ex.getMessage());
			return null;
		}
	}

	public Integer getLastHistoryID(Date date, String symbol) {
		Integer retValue = null;
		try {
			retValue = (Integer) entityManager
					.createQuery(
							"select h.id from hu.myai.model.db.AxHistory h where h.symbol = :symbol and h.date < :date order by date desc")
					.setParameter("symbol", symbol).setParameter("date", date).setMaxResults(1).getSingleResult();
		} catch (NoResultException ex) {
			log.error("Error - getLastHistoryRecord - no last history record found for " + symbol + ": "
					+ ex.getMessage());
		}
		return retValue;
	}

	public AxHistory getLastHistory(Date date, String symbol) {
		AxHistory retValue = null;
		try {
			retValue = (AxHistory) entityManager
					.createQuery(
							"select h from hu.myai.model.db.AxHistory h where h.symbol = :symbol and h.date < :date order by date desc")
					.setParameter("symbol", symbol).setParameter("date", date).setMaxResults(1).getSingleResult();
		} catch (NoResultException ex) {
			log.error("Error - getLastHistoryRecord - no last history record found for " + symbol + ": "
					+ ex.getMessage());
		}
		return retValue;
	}

	public long recordsInBetween(String symbol, Date start, Date end) {
		Long retValue = new Long(0);

		if (start.after(end)) {
			Date tmp = end;
			end = start;
			start = tmp;
		}

		try {
			retValue = (Long) entityManager
					.createQuery(
							"select count(h) from hu.myai.model.db.AxHistory h where h.symbol = :symbol and h.date between :start and :end")
					.setParameter("symbol", symbol).setParameter("start", start).setParameter("end", end)
					.getSingleResult();
		} catch (NoResultException ex) {
			log.error("Error - getLastHistoryRecord - no last history record found for " + symbol + ": "
					+ ex.getMessage());
		}
		return retValue.longValue();
	}
}
